
package com.feib.soeasy.job.dao.hibernate;

import java.util.Date;
import java.util.List;

import javax.annotation.Resource;

import org.hibernate.Query;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Example;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import org.springframework.stereotype.Repository;

import com.feib.soeasy.job.dao.ImportDao;
import com.feib.soeasy.model.ColumnValidateError;
import com.feib.soeasy.model.Group;
import com.feib.soeasy.model.MailQueue;
import com.feib.soeasy.model.Payment;
import com.feib.soeasy.model.UploadFile;


/**
 * @title (#)ImportHibernateDao.java<br>
 * @description <br>
 * @author Jimmy Liu<br>
 * @version 1.0.0 2010/11/25
 * @copyright Far Eastern International Bank Copyright (c) 2010<br>
 * @2010/11/28 create by Jimmy Liu<br>
 */
@Repository("importDao")
public class ImportHibernateDao extends HibernateDaoSupport implements ImportDao{

    @Resource(name = "sessionFactory")
    public void setMySessionFactory(SessionFactory sessionFactory) {
        super.setSessionFactory(sessionFactory);
    }
    
    public UploadFile queryUploadFileByPk(Long uploadFilePk)
    {
        UploadFile result = this.getHibernateTemplate().get(UploadFile.class, uploadFilePk);
        return result;
    }

    /* (non-Javadoc)
     * @see com.feib.soeasy.dao.ImportDao#queryFirstUnCheckUploadFile()
     */
    @SuppressWarnings("unchecked")
    @Override
    public UploadFile queryFirstUnCheckUploadFile() {
        
        DetachedCriteria criteria = DetachedCriteria.forClass(UploadFile.class);
        criteria.setResultTransformer(DetachedCriteria.DISTINCT_ROOT_ENTITY);

        UploadFile entity = new UploadFile();
        entity.setFileStatus(UploadFile.FILE_STATUS_UN_CHECK);
        
        Example example = Example.create(entity);
        criteria.add(example);
        
        criteria.addOrder(Order.asc("uploadDate"));
        
        List<UploadFile> uploadFiles = this.getHibernateTemplate().findByCriteria(criteria, 0, 1);
        
        if (null == uploadFiles || 0 == uploadFiles.size())
            return null;
        
        return uploadFiles.get(0);
    }

    /* (non-Javadoc)
     * @see com.feib.soeasy.dao.ImportDao#updateUploadFileStatus(com.feib.soeasy.model.UploadFile)
     */
    @Override
    public void updateUploadFileStatus(UploadFile uploadFile) {
        
        StringBuffer queryString = new StringBuffer();
        queryString.append("update UploadFile set fileStatus = ").append(uploadFile.getFileStatus());
        queryString.append(" where uploadFilePk = ").append(uploadFile.getUploadFilePk());        
        this.getHibernateTemplate().bulkUpdate(queryString.toString());
    }

    /* (non-Javadoc)
     * @see com.feib.soeasy.dao.ImportDao#deletePaymentsByUploadFile(com.feib.soeasy.model.UploadFile)
     */
    @Override
    public void deletePaymentsByUploadFile(UploadFile uploadFile) {
        StringBuffer queryString = new StringBuffer();

        queryString.append("delete PaymentItem as item ");
        queryString.append("where item.payment in (");
        queryString.append("select payment.paymentPk from Payment payment ");
        queryString.append("where payment.uploadFile = ").append(uploadFile.getUploadFilePk());
        queryString.append(") ");
        
        this.getHibernateTemplate().bulkUpdate(queryString.toString());
        queryString = new StringBuffer();
        queryString.append("delete Payment where uploadFile.uploadFilePk = ").append(uploadFile.getUploadFilePk());        
        this.getHibernateTemplate().bulkUpdate(queryString.toString());
    }

    /* (non-Javadoc)
     * @see com.feib.soeasy.dao.ImportDao#addPayments(java.util.List)
     */
    @Override
    public void addPayments(List<Payment> payments) {
        this.getHibernateTemplate().saveOrUpdateAll(payments);
        
    }

    /* (non-Javadoc)
     * @see com.feib.soeasy.dao.ImportDao#addColumnValidateErrors(java.util.List)
     */
    @Override
    public void addColumnValidateErrors(List<ColumnValidateError> columnValidateErrors) {
        
        this.getHibernateTemplate().saveOrUpdateAll(columnValidateErrors);
        
    }
    
    @SuppressWarnings("unchecked")
    public List<ColumnValidateError> getColumnValidateErrors(UploadFile uploadFile)
    {        
        StringBuffer queryString = new StringBuffer();

        queryString.append("from ColumnValidateError where uploadFile = :uploadFile order by lineNumber");
        
        Query query = this.getSession().createQuery(queryString.toString());
        query.setParameter("uploadFile", uploadFile);
        
        return query.list();
    }

    /* (non-Javadoc)
     * @see com.feib.soeasy.dao.ImportDao#isDuplicateVirtualAccount(java.lang.String, java.util.Date)
     */
    @Override
    public boolean isDuplicateVirtualAccount(String virtualAccount, Date payDue) {
        
        DetachedCriteria criteria = DetachedCriteria.forClass(Payment.class);
        criteria.setResultTransformer(DetachedCriteria.DISTINCT_ROOT_ENTITY);
        Payment entity = new Payment();
        entity.setVirtualAccount(virtualAccount);
        Example example = Example.create(entity);
        example.excludeZeroes();
        criteria.add(example);
        criteria.add(Restrictions.ne("paymentStatus", 4));
                
        criteria.setProjection(Projections.rowCount());
        return 0 < ((Number)this.getHibernateTemplate().findByCriteria(criteria).iterator().next()).intValue();
    }

    /* (non-Javadoc)
     * @see com.feib.soeasy.job.dao.ImportDao#addMailQueue(com.feib.soeasy.model.MailQueue)
     */
    @Override
    public MailQueue addMailQueue(MailQueue mailQueue) {
        this.getHibernateTemplate().saveOrUpdate(mailQueue);
        return mailQueue;
    }
    
    @SuppressWarnings("unchecked")
    public List<String> getConfirmUserEmail(Group group)
    {
        StringBuffer queryString = new StringBuffer();

        queryString.append("select user.userEmail from User user inner join user.roles role inner join role.menuItems menuItem ");
        queryString.append("where user.group = :group ");
        queryString.append("and menuItem.menuId = 'SOEZ2003' ");
        Query query = this.getSession().createQuery(queryString.toString());
        query.setParameter("group", group);
        return query.list();
    }

    /* (non-Javadoc)
     * @see com.feib.soeasy.job.dao.ImportDao#updateUploadFileTotals(com.feib.soeasy.model.UploadFile)
     */
    @Override
    public void updateUploadFileTotals(UploadFile uploadFile) {
        
        StringBuffer queryString = new StringBuffer();
        queryString.append("update UploadFile set totalAmount = ? ");
        queryString.append(" , totalItems = ? ");
        queryString.append(" where uploadFilePk = ").append(uploadFile.getUploadFilePk());        
        this.getHibernateTemplate().bulkUpdate(queryString.toString(), uploadFile.getTotalAmount(), uploadFile.getTotalItems());
        
    }
}
